﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SCSService
{
    public class ConfigService
    {

        /// <summary>
        /// Create Support 
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        /// <summary>
        public void UpdateSupport(Support type)
        {
            using (var data = new DataDataContext())
            {
                var typeProduct = (from p in data.Supports
                                   where p.SupportID == type.SupportID
                                   select p).SingleOrDefault();
                if (type.SupportAccount != null)
                {
                    typeProduct.SupportAccount = type.SupportAccount;
                }

                if (type.SupportName != null)
                {
                    typeProduct.SupportName = type.SupportName;
                }

                if (type.SupportActive != null)
                {
                    typeProduct.SupportActive = type.SupportActive;
                }

                if (type.SupportText != null)
                {
                    typeProduct.SupportText = type.SupportText;
                }

                if (type.SupportType != null)
                {
                    typeProduct.SupportType = type.SupportType;
                }

                if (type.LanguagesID != null)
                {
                    typeProduct.LanguagesID = type.LanguagesID;
                }

                if (type.SiteGuid != null)
                {
                    typeProduct.SiteGuid = type.SiteGuid;
                }



                data.SubmitChanges();
            }
        }
        public int CreateSupport(Support prtSupport)
        {
            using (var data = new DataDataContext())
            {
                data.Supports.InsertOnSubmit(prtSupport);
                data.SubmitChanges();

                return prtSupport.SupportID;
            }
        }
        public void DeleteSupport(int? typeId)
        {
            using (var data = new DataDataContext())
            {
                var Items = (from p in data.Supports
                                  where p.SupportID == typeId
                                  select p).FirstOrDefault();
                data.Supports.DeleteOnSubmit(Items);
                data.SubmitChanges();
            }
        }
        public List<Support> GetListSupport(string SupportName,bool? SupportActive,int? LanguagesID,int? Sort,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {

                var result = (from p in data.Supports
                              where p.SiteGuid == siteGuid && p.LanguagesID == LanguagesID
                              select p);
                if (!string.IsNullOrEmpty(SupportName))
                {
                   result = result.Where(p=>p.SupportName==SupportName);
                
                }
                if (Sort != null)
                {
                    result = result.Where(p => p.Sort==Sort );
                }
                if (SupportActive != null)
                {
                    result = result.Where(p => p.SupportActive == SupportActive);

                }
                
                return result.ToList();
            }

        }
        public Support GetListSupportById(int id, int LanguageID, int siteGuid)
        { 
            using(var data=new DataDataContext() )
            {
                var type=(from sup in data.Supports
                          where sup.SupportID == id && sup.LanguagesID == LanguageID && sup.SiteGuid == siteGuid

                              select sup).FirstOrDefault();
                return type;
            }
        
        }

        /// Get Module for site By siteguid and ModuleName
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>

        public int GetModuleIDbySiteAndNameModule(int id, string nameModule)
        {
            using (var data = new DataDataContext())
            {
                var result = (from p in data.Config_Moduls
                              join s in data.System_Website_Moduls on p.ModulID equals s.ModulID
                              where s.SiteGuid == id && p.ModulName == nameModule
                              select p).FirstOrDefault();
                if (result != null)
                    return result.ModulID;
                else
                    return 0;
            }
        }
        
        /// <summary>
        /// Get PageID for site by ModuleID and pageName
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <returns></returns>


        public int GetPageIdbyModuleAndNamePage(int id, string namePage)
        {
            using (var data = new DataDataContext())
            {
                var result = (from p in data.Config_Pages
                              where p.ModulID == id && p.PageName == namePage
                              select p).FirstOrDefault();
                if (result != null)
                    return result.PageID;
                else
                    return 0;
            }
        }

        /// <summary>
        /// Get List Modul of Website by siteguid
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Config_Modul> GetListModulBySiteGuid(int siteGuid,int typeModul)
        {
            using (var data = new DataDataContext())
            {               
                List<Config_Modul> listModul=new List<Config_Modul>();
                listModul=(from p in data.Config_Moduls
                        join wm in data.System_Website_Moduls on p.ModulID equals wm.ModulID into r0
                        from e0 in r0.DefaultIfEmpty()
                        join w in data.System_Websites on e0.SiteGuid equals w.SiteGuid into r1
                        from e1 in r1.DefaultIfEmpty()
                        where e1.SiteGuid==siteGuid && p.TypeModulID==typeModul
                        select p).ToList();

                return listModul;     
            }
        }

        /// <summary>
        /// Get list All Modul in siteguid
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Config_Modul> GetListAllModulBySiteGuid(int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                List<Config_Modul> listModul = new List<Config_Modul>();
                listModul = (from p in data.Config_Moduls
                             join wm in data.System_Website_Moduls on p.ModulID equals wm.ModulID into r0
                             from e0 in r0.DefaultIfEmpty()
                             join w in data.System_Websites on e0.SiteGuid equals w.SiteGuid into r1
                             from e1 in r1.DefaultIfEmpty()
                             where e1.SiteGuid == siteGuid
                             select p).ToList();

                return listModul;
            }
        }

        /// <summary>
        /// Get List Parent Page In Modul
        /// </summary>
        /// <param name="modulID"></param>
        /// <returns></returns>
        public List<Config_Page> GetListPageParentInModul(int modulID)
        {
            using (var data = new DataDataContext())
            {
                var page = (from p in data.Config_Pages
                            where p.ModulID == modulID && p.PageParent == null
                            select p).ToList();
                return page;
            }
        }

        /// <summary>
        /// Get List Website backEnd by UserID
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public List<System_Website> GetListWebsiteByUser(int userID,int perID)
        {
            using (var data = new DataDataContext())
            {
                var listSite = (from w in data.System_Websites
                                join swu in data.Security_Website_Users on w.SiteGuid equals swu.SiteGuid into r0
                                from e0 in r0.DefaultIfEmpty()
                                join u in data.Security_Users on e0.UserID equals u.UserID into r1
                                from e1 in r1.DefaultIfEmpty()
                                join r in data.Security_Roles on e1.RoleID equals r.RoleID into r2
                                from e2 in r2.DefaultIfEmpty()
                                join rp in data.Security_Role_Permissions on e2.RoleID equals rp.RoleID into r3
                                from e3 in r3.DefaultIfEmpty()
                                join p in data.Security_Permissions on e3.PerID equals p.PerID into r4
                                from e4 in r4.DefaultIfEmpty()
                                where e1.UserID==userID && e4.PerID==perID
                                orderby e2.Priority ascending
                                select w);

                return listSite.ToList();
            }
        }

        /// <summary>
        /// Get site by domain
        /// </summary>
        /// <param name="domain"></param>
        /// <returns></returns>
        public System_Website GetWebsiteByDomain(string domain)
        {
            using (var data = new DataDataContext())
            {
                var website = (from p in data.System_Websites
                               where p.domain == domain
                               select p).SingleOrDefault();
                return website;
            }
        }

        //
        public Config_MasterPage GetMasterPageByPageID(int id,int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var result = (from p in data.Config_Pages
                              join pmp in data.Config_Page_MasterPages on p.PageID equals pmp.PageID into r1
                              from e1 in r1.DefaultIfEmpty()
                              join m in data.Config_MasterPages on e1.MasterPageID equals m.MasterPageID into r0
                              from e0 in r0.DefaultIfEmpty()
                              where p.PageID == id && e0.SiteGuid==siteGuid
                              orderby p.PageID descending
                              select e0).FirstOrDefault();

                return result;
            }
        }

        /// <summary>
        /// Check PageId in Website
        /// </summary>
        /// <param name="siteguid"></param>
        /// <param name="pageid"></param>
        /// <returns></returns>
        public bool CheckViewPage(int siteguid, int pageid)
        {
            using (var data = new DataDataContext())
            {
                var result = (from p in data.Config_Page_Websites
                              where p.SiteGuid == siteguid && p.PageID == pageid
                              select p);
                if (result != null)
                {
                    if (result.Count() > 0)
                        return true;
                    else
                        return false;
                }
                else
                    return false;
            }
        }
        /// <summary>
        /// Get List Page in fontend
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Config_Page> GetListPageInWebsite(int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Config_Page_Websites
                            join cp in data.Config_Pages on p.PageID equals cp.PageID into r0
                            from e0 in r0.DefaultIfEmpty()
                            join m in data.Config_Moduls on e0.ModulID equals m.ModulID into r1
                            from e1 in r1.DefaultIfEmpty()
                            where p.SiteGuid == siteGuid && e1.TypeModulID==2
                            select e0);
                return list.ToList();
            }
        }

        /// <summary>
        /// Get List All Page in site
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Config_Page> GetListAllPageInWebsite(int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Config_Page_Websites
                            join cp in data.Config_Pages on p.PageID equals cp.PageID into r0
                            from e0 in r0.DefaultIfEmpty()
                            join m in data.Config_Moduls on e0.ModulID equals m.ModulID into r1
                            from e1 in r1.DefaultIfEmpty()
                            where p.SiteGuid == siteGuid 
                            select e0);
                return list.ToList();
            }
        }

        /// <summary>
        /// Get List Control in Page
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Config_Control> GetListControlInPage(int id,int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var result = (from c in data.Config_Controls
                              join cp in data.Config_Page_Controls on c.ControlID equals cp.ControlID into r0
                              from e0 in r0.DefaultIfEmpty()
                              where e0.PageID == id && e0.SiteGuid==siteGuid
                              orderby e0.SortOrder ascending
                              select c).ToList();
                return result;
            }
        }
        /// <summary>
        /// Get List Config_Page_Control by siteGuid
        /// </summary>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Config_Page_Control> GetListControlInSiteGuid(int siteGuid)
        {
            using (var data = new DataDataContext())
            {
            
                var list = (from c in data.Config_Page_Controls
                            where c.SiteGuid == siteGuid
                            select c).ToList();
                return list;
            }
        }

        /// <summary>
        /// Get Article Static by ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Article_Static GetArticleStaticByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var art = (from p in data.Article_Statics
                           where p.StaticID == id
                           select p).FirstOrDefault();
                return art;
            }
        }
        /// <summary>
        /// Get List ArticleStatic by list Control ID
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        //public List<Article_Static> GetListArticleStaticByListControlID(List<Config_Control> listControl, int LanguagesID, int siteGuid)
        //{
        //    using (var data = new DataDataContext())
        //    {
        //        var listReturn = (from p in data.Article_Statics
        //                          where p.ControlID != null && p.LanguagesID==LanguagesID && p.SiteGuid==siteGuid
        //                          select p);

        //        var listID = (from p in listControl
        //                      select p.ControlID).ToList();

        //        listReturn = listReturn.Where(p => listID.Contains((int)p.ControlID));


        //        return listReturn.ToList();
        //    }
        //}

        /// <summary>
        /// Đệ quy list ID Menu
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public StringBuilder GetListMenuByID(ref StringBuilder sb, int id, int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var menu = (from p in data.Menus
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid && p.PartnerMenuId == id
                            select p.MenuId).ToList();
                foreach (var item in menu)
                {
                    sb.Append(item + ",");
                    if (menu.Count() > 0)
                    {
                        GetListMenuByID(ref sb, item, LanguagesID, siteGuid);
                    }
                }

                return sb;
            }
        }
        /// <summary>
        /// Get Menu by Id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Menu GetMenuByID(int? id)
        {
            using (var data = new DataDataContext())
            {
                var menu = (from p in data.Menus
                            where p.MenuId == id
                            select p).FirstOrDefault();
                return menu;
            }
        }

        public Config_Page GetPagebyId(int? id)
        {
            using (var data = new DataDataContext())
            {
                var page = (from p in data.Config_Pages
                            where p.PageID==id
                            select p).FirstOrDefault();
                return page;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="LanguagesID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="url"></param>
        /// <returns></returns>
        public Menu GetMenuBySiteGuidAndLanguageIDAndUrl(int LanguagesID, int siteGuid, string url)
        {
            using (var data = new DataDataContext())
            {
                var menu = (from p in data.Menus
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid && p.MenuUrl == url
                            select p).FirstOrDefault();
                return menu;
            }
        }

        public List<Menu> GetListMenuByParent(int? parentID, int LanguagesID, int siteGuid,int status)
        {
            using (var data = new DataDataContext())
            {
                var item = (from p in data.Menus
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid
                            select p);
                if (parentID != null)
                {
                    item = item.Where(p => p.PartnerMenuId == parentID);
                }
                else
                {
                    item = item.Where(p => p.PartnerMenuId == null);
                }

                if (status != -1)
                {
                    item = item.Where(p => p.MenuStatus == status);
                }

                item = (from e in item
                        orderby e.SortOrder ascending
                        select e);

                return item.ToList();
            }
        }

        public List<Menu> GetListMenuBySiteGuid(int siteGuid, int? status)
        {
            using (var data = new DataDataContext())
            {
                var item = (from p in data.Menus
                            where p.SiteGuid == siteGuid
                            select p);
               
                if (status != null)
                {
                    item = item.Where(p => p.MenuStatus == status);
                }

                item = (from e in item
                        orderby e.SortOrder ascending
                        select e);

                return item.ToList();
            }
        }
        /// <summary>
        /// Get Menu By ParentID 
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="languageID"></param>
        /// <param name="siteGuid"></param>
        /// <param name="MenuName"></param>
        /// <param name="MenuText"></param>
        /// <param name="MenuUrl"></param>
        /// <param name="MenuStatus"></param>
        /// <param name="SortOrder"></param>
        /// <param name="TypeMenuID"></param>
        /// <returns></returns>
        public List<Menu> GetListMenuByParentIDSearch(int? parentID, int languageID, int siteGuid, string MenuName, string MenuText, string MenuUrl, int? MenuStatus, int? SortOrder, int? TypeMenuID)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Menus
                            where p.LanguagesID == languageID && p.SiteGuid == siteGuid
                            select p);

                if (parentID != null)
                {
                    list = list.Where(p => p.PartnerMenuId == parentID);
                }
                else
                {
                    list = list.Where(p => p.PartnerMenuId == null);
                }

                if (MenuName != string.Empty && MenuName != null)
                {
                    list = list.Where(p => p.MenuName.Contains(MenuName));
                }

                if (MenuText != string.Empty && MenuText != null)
                {
                    list = list.Where(p => p.MenuText.Contains(MenuText));
                }

                if (MenuUrl != string.Empty && MenuUrl != null)
                {
                    list = list.Where(p => p.MenuUrl.Contains(MenuUrl));
                }

                if (MenuStatus != null)
                {
                    list = list.Where(p => p.MenuStatus == MenuStatus);
                }

                if (SortOrder != null)
                {
                    list = list.Where(p => p.SortOrder == SortOrder);
                }

                if (TypeMenuID != null && TypeMenuID != null)
                {
                    list = list.Where(p => p.TypeMenuID==TypeMenuID);
                }

               
                list = (from p in list
                        orderby p.SortOrder ascending
                        select p);

                return list.ToList();
            }
        }

        public int GetCountMenu(int LanguagesID, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var item = (from p in data.Menus
                            where p.LanguagesID == LanguagesID && p.SiteGuid == siteGuid
                            select p).Count();


                return item;
            }
        }

        public int CreateMenu(Menu menu)
        {
            using (var data = new DataDataContext())
            {
                data.Menus.InsertOnSubmit(menu);
                data.SubmitChanges();

                return menu.MenuId;
            }
        }

        public void UpdateMenu(Menu menu)
        {
            using (var data = new DataDataContext())
            {
                var me = (from p in data.Menus
                                where p.MenuId == menu.MenuId
                                select p).SingleOrDefault();
                if (menu.MenuName != string.Empty)
                {
                    me.MenuName = menu.MenuName;
                }

                if (menu.MenuText != string.Empty)
                {
                    me.MenuText = menu.MenuText;
                }

                if (menu.MenuUrl != null)
                {
                    me.MenuUrl = menu.MenuUrl;
                }

                if (menu.Images != null)
                {
                    me.Images = menu.Images;
                }

                if (menu.SortOrder != null)
                {
                    me.SortOrder = menu.SortOrder;
                }

                if (menu.PartnerMenuId != null)
                {
                    me.PartnerMenuId = menu.PartnerMenuId;
                }

                if (menu.LanguagesID != null)
                {
                    me.LanguagesID = menu.LanguagesID;
                }

                me.SiteGuid = menu.SiteGuid;

                data.SubmitChanges();
            }
        }

        public void UpdateStatusMenu(int? id, byte? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var menu = (from p in data.Menus
                                where p.MenuId == id
                                select p).FirstOrDefault();
                    if (menu != null)
                    {
                        if (menu.MenuStatus != newStatus)
                            menu.MenuStatus = newStatus;

                        data.SubmitChanges();
                    }
                }
            }
        }

        public void UpdateSortOrderOfMenu(int? id, int? newSort)
        {
            using (var data = new DataDataContext())
            {
                var menu = (from p in data.Menus
                            where p.MenuId == id
                            select p).FirstOrDefault();
                if (menu != null && newSort != null)
                {
                    if (menu.SortOrder != newSort)
                        menu.SortOrder = (int)newSort;

                    data.SubmitChanges();
                }
            }
        }

        public void DeleteMenu(int id)
        {
            using (var data = new DataDataContext())
            {
                var menu = (from p in data.Menus
                            where p.MenuId == id
                            select p).FirstOrDefault();
                data.Menus.DeleteOnSubmit(menu);
                data.SubmitChanges();
            }
        }


        public int CreateMetadata(Config_Metadata meta)
        {
            using (var data = new DataDataContext())
            {
                data.Config_Metadatas.InsertOnSubmit(meta);
                data.SubmitChanges();

                return meta.MetaID;
            }
        }

        public Config_Metadata GetMetadataByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var meta = (from p in data.Config_Metadatas
                            where p.MetaID==id
                            select p).FirstOrDefault();

                return meta;
            }
        }

        public void UpdateMetadata(Config_Metadata meatadata)
        {
            using (var data = new DataDataContext())
            {
                var meta = (from p in data.Config_Metadatas
                            where p.MetaID == meatadata.MetaID
                          select p).SingleOrDefault();

                if (meatadata.MetaTitle != string.Empty)
                {
                    meta.MetaTitle = meatadata.MetaTitle;
                }

                if (meatadata.MetaKeywords != string.Empty)
                {
                    meta.MetaKeywords = meatadata.MetaKeywords;
                }

                if (meatadata.MetaDescription != null)
                {
                    meta.MetaDescription = meatadata.MetaDescription;
                }

                if (meatadata.PageID != null)
                {
                    meta.PageID = meatadata.PageID;
                }

                if (meatadata.siteGuid != null)
                {
                    meta.siteGuid = meatadata.siteGuid;
                }

                if (meatadata.Note != null)
                {
                    meta.Note = meatadata.Note;
                }

                if (meatadata.LanguagesID != null)
                {
                    meta.LanguagesID = meatadata.LanguagesID;
                }

                if (meatadata.MetaPoweredBy != null)
                {
                    meta.MetaPoweredBy = meatadata.MetaPoweredBy;
                }

                if (meatadata.MetaDcCreator != null)
                {
                    meta.MetaDcCreator = meatadata.MetaDcCreator;
                }

                if (meatadata.MetaAuthor != null)
                {
                    meta.MetaAuthor = meatadata.MetaAuthor;
                }

                if (meatadata.MetaRobots != null)
                {
                    meta.MetaRobots = meatadata.MetaRobots;
                }

                if (meatadata.MenuId != null)
                {
                    meta.MenuId = meatadata.MenuId;
                }

                if (meatadata.Param != null)
                {
                    meta.Param = meatadata.Param;
                }

                if (meatadata.MetaDctermsCreated != null)
                {
                    meta.MetaDctermsCreated = meatadata.MetaDctermsCreated;
                }

                if (meatadata.MetaDcctermsModified != null)
                {
                    meta.MetaDcctermsModified = meatadata.MetaDcctermsModified;
                }

                if (meatadata.RevisitAfter != null)
                {
                    meta.RevisitAfter = meatadata.RevisitAfter;
                }

                if (meatadata.DatePublication != null)
                {
                    meta.DatePublication = meatadata.DatePublication;
                }

                if (meatadata.Homepage != null)
                {
                    meta.Homepage = meatadata.Homepage;
                }

                if (meatadata.DcLanguage != null)
                {
                    meta.DcLanguage = meatadata.DcLanguage;
                }

                data.SubmitChanges();
            }
        }

        public List<Config_Metadata> GetListMetadata(string TitleSearch, int? siteGuid, int langauesID)
        {
            using (var data = new DataDataContext())
            {
                var meta = (from p in data.Config_Metadatas
                            where p.siteGuid==siteGuid && p.LanguagesID==langauesID
                            select p);

                if (TitleSearch != string.Empty && TitleSearch!=null)
                {
                    meta = meta.Where(p => p.MetaTitle.Contains(TitleSearch));
                }

                return meta.ToList();
            }
        }

        public List<Config_Metadata> GetListMetadataBysiteGuid(int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var meta = (from p in data.Config_Metadatas
                            where p.siteGuid == siteGuid
                            select p);

               
                return meta.ToList();
            }
        }

        public void DeleteMetadata(int id)
        {
            using (var data = new DataDataContext())
            {
                var menu = (from p in data.Config_Metadatas
                            where p.MetaID == id
                            select p).FirstOrDefault();
                data.Config_Metadatas.DeleteOnSubmit(menu);
                data.SubmitChanges();
            }
        }
        //public Config GetConfigByArticleStaticID(int id)
        //{
        //    using (var data = new DataDataContext())
        //    {
        //        var item = (from s in data.Article_Statics
        //                    join cc in data.Config_Controls on s.ControlID equals cc.ControlID into r0
        //                    from e0 in r0.DefaultIfEmpty()
        //                    join cp in data.Config_Page_Controls on e0.ControlID equals cp.ControlID into r1
        //                    from e1 in r1.DefaultIfEmpty()
        //                    join c in data.Configs on e1.ConfigID equals c.ID into r2
        //                    from e2 in r2.DefaultIfEmpty()
        //                    where s.StaticID == id
        //                    select e2).FirstOrDefault();

        //        return item;
        //    }
        //}

        public Config_Metadata GetMetadataByPageIDAndMenuIdAndParam(int? pageId, int? menuID, string param,int? siteGuid,int? LanguageId)
        {
            using (var data = new DataDataContext())
            {
                var meta = (from p in data.Config_Metadatas
                            where p.PageID == pageId && p.siteGuid == siteGuid && p.LanguagesID == LanguageId
                            select p);
                

                if (param != null && param != string.Empty)
                {
                    meta = meta.Where(p => p.Param.Contains(param));

                    if (menuID != null)
                    {
                        meta = meta.Where(p => p.MenuId == menuID);
                    }
                }

                return meta.FirstOrDefault();

            }
        }

        public int CreateWebshop(System_Website shop)
        {
            using (var data = new DataDataContext())
            {
                data.System_Websites.InsertOnSubmit(shop);
                data.SubmitChanges();

                return shop.SiteGuid;
            }
        }

        public int CreateMasterPage(Config_MasterPage master)
        {
            using (var data = new DataDataContext())
            {
                data.Config_MasterPages.InsertOnSubmit(master);
                data.SubmitChanges();

                return master.MasterPageID;
            }
        }


        public int CreateSystemWebsiteModule(System_Website_Modul swm)
        {
            using (var data = new DataDataContext())
            {
                data.System_Website_Moduls.InsertOnSubmit(swm);
                data.SubmitChanges();

                return swm.ID;
            }
        }

        public int CreateConfigPageWebsite(Config_Page_Website cpw)
        {
            using (var data = new DataDataContext())
            {
                data.Config_Page_Websites.InsertOnSubmit(cpw);
                data.SubmitChanges();

                return cpw.ConfigPageWebsiteID;
            }
        }

        public int CreateConfigPageMasterPage(Config_Page_MasterPage cpm)
        {
            using (var data = new DataDataContext())
            {
                data.Config_Page_MasterPages.InsertOnSubmit(cpm);
                data.SubmitChanges();

                return cpm.PageMasterPageID;
            }
        }

        public int CreateConfigPageControl(Config_Page_Control cpc)
        {
            using (var data = new DataDataContext())
            {
                data.Config_Page_Controls.InsertOnSubmit(cpc);
                data.SubmitChanges();

                return cpc.ID;
            }
        }

        public List<SCSService.Config_Page> GetListPageByParentIDAndModuleID(int? parentID, int? moduleID)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Config_Pages
                            select p);

                if (parentID != null)
                {
                    list = list.Where(p => p.PageParent == parentID);
                }

                if (moduleID != null)
                {
                    list = list.Where(p => p.ModulID == moduleID);
                }

                return list.ToList();
            }
        }

        public SCSService.Config_Modul GetModuleByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var module = (from p in data.Config_Moduls
                              where p.ModulID == id
                              select p).FirstOrDefault();
                return module;
            }
        }
        public List<Config_Modul> GetAllModule()
        {
            using (var data = new DataDataContext())
            {
                var module = (from p in data.Config_Moduls
                              select p).ToList();
                return module;
            }
        }

        public List<Config_Modul> GetListModulforUserLogin(int siteGuid, int userID, int typeModul)
        {
            using (var data = new DataDataContext())
            {
                var user = (from p in data.Security_Users
                            where p.UserID == userID
                            select p).FirstOrDefault();

                List<int> listPerID = (from u in data.Security_Users
                                       join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                       from t0 in e0.DefaultIfEmpty()
                                       join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                       from t1 in e1.DefaultIfEmpty()
                                       join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                       from t2 in e2.DefaultIfEmpty()
                                       where t1.RoleID == user.RoleID
                                       orderby t0.Priority ascending
                                       select t1.PerID).Distinct().ToList();

                List<int> ListModuleByPer = (from p in data.Config_Moduls
                                             join pm in data.Security_Module_Permissions on p.ModulID equals pm.ModulID into r0
                                             from e0 in r0.DefaultIfEmpty()
                                             where listPerID.Contains(e0.PerID)
                                             select e0.ModulID).Distinct().ToList();

                List<Config_Modul> listModul = new List<Config_Modul>();
                listModul = (from p in data.Config_Moduls
                             join wm in data.System_Website_Moduls on p.ModulID equals wm.ModulID into r0
                             from e0 in r0.DefaultIfEmpty()
                             where e0.SiteGuid == siteGuid && p.TypeModulID == typeModul && ListModuleByPer.Contains(p.ModulID)
                             select p).Distinct().ToList();

                return listModul;
            }
        }

        public List<Config_Page> GetListPageParentInModulforUserLogin(int modulID, int siteGuid, int userID)
        {
            using (var data = new DataDataContext())
            {
                var user = (from p in data.Security_Users
                            where p.UserID == userID
                            select p).FirstOrDefault();

                List<int> listPerID = (from u in data.Security_Users
                                       join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                       from t0 in e0.DefaultIfEmpty()
                                       join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                       from t1 in e1.DefaultIfEmpty()
                                       join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                       from t2 in e2.DefaultIfEmpty()
                                       where t1.RoleID == user.RoleID
                                       orderby t0.Priority ascending
                                       select t1.PerID).Distinct().ToList();

                List<int> ListPageByPer = (from p in data.Config_Pages
                                           join pm in data.Security_Page_Permissions on p.PageID equals pm.PageID into r0
                                           from e0 in r0.DefaultIfEmpty()
                                           where listPerID.Contains(e0.PerID)
                                           select e0.PageID).Distinct().ToList();

                List<Config_Page> listPage = new List<Config_Page>();
                listPage = (from p in data.Config_Pages
                            join wm in data.Config_Page_Websites on p.PageID equals wm.PageID into r0
                            from e0 in r0.DefaultIfEmpty()
                            where e0.SiteGuid == siteGuid && p.ModulID == modulID && p.PageParent == null //&& ListPageByPer.Contains(p.PageID) && p.PageParent==null
                            select p).Distinct().ToList();

                var result = (from p in listPage
                              where ListPageByPer.Contains(p.PageID) && p.PageParent == null
                              select p);

                return listPage;
            }
        }

        public List<Config_Page> GetListPageforUserLogin(int siteGuid, int userID)
        {
            using (var data = new DataDataContext())
            {
                var user = (from p in data.Security_Users
                            where p.UserID == userID
                            select p).FirstOrDefault();

                List<int> listPerID = (from u in data.Security_Users
                                       join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                       from t0 in e0.DefaultIfEmpty()
                                       join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                       from t1 in e1.DefaultIfEmpty()
                                       join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                       from t2 in e2.DefaultIfEmpty()
                                       where t1.RoleID == user.RoleID
                                       orderby t0.Priority ascending
                                       select t1.PerID).ToList();

                List<int> ListPageByPer = (from p in data.Config_Pages
                                           join pm in data.Security_Page_Permissions on p.PageID equals pm.PageID into r0
                                           from e0 in r0.DefaultIfEmpty()
                                           where listPerID.Contains(e0.PageID)
                                           select e0.PageID).ToList();

                List<Config_Page> listPage = new List<Config_Page>();
                listPage = (from p in data.Config_Pages
                            join wm in data.Config_Page_Websites on p.PageID equals wm.PageID into r0
                            from e0 in r0.DefaultIfEmpty()
                            where e0.SiteGuid == siteGuid && ListPageByPer.Contains(p.PageID)
                            select p).ToList();

                return listPage;
            }
        }
    }
}
